﻿namespace Triad.DAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    
    public class TriadDataReader
    {
        private readonly string strConn;

        /// <summary>
        /// Initializing Object
        /// </summary>
        /// <param name="p_ConnStr">Connection String</param>
        /// <param name="p_ConnStrID">The ID of the connection string in the Web.Config.</param>
        /// <remarks></remarks>
        public TriadDataReader(string p_ConnStr, string p_ConnStrID)
        {
            strConn = String.IsNullOrEmpty(p_ConnStrID) ? p_ConnStr : Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings[p_ConnStrID]);
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="TriadDataReader"/> class.
        /// </summary>
        public TriadDataReader()
        {
            strConn = Convert.ToString(System.Configuration.ConfigurationManager.ConnectionStrings["TriadFramework"]);
        }

        /// <summary> 
        /// Execute stored procedure using SqlDataReader 
        /// </summary> 
        /// <param name="p_StoredProc">Stored Procedure Name</param> 
        /// <param name="p_SqlParameters">Sql Parameters</param> 
        /// <param name="p_ErrMsg">Error Message</param> 
        /// <returns>Datatable from stored procedure</returns> 
        /// <remarks></remarks> 
        public DataTable GetData(string p_StoredProc, SqlParameter[] p_SqlParameters, ref string p_ErrMsg) 
    { 
        SqlConnection connection = new SqlConnection(strConn); 
       
        try { 
            connection.Open(); 
            using (SqlCommand Command = new SqlCommand(p_StoredProc, connection)) { 
                Command.CommandType = CommandType.StoredProcedure; 
                if ((p_SqlParameters != null)) { 
                    foreach ( SqlParameter s_SqlParameter in p_SqlParameters) { 
                        Command.Parameters.Add(s_SqlParameter); 
                    } 
                }
                SqlDataReader reader = Command.ExecuteReader(); 
                return GetTable(reader); 
            } 
        } 
        catch (Exception ex) { 
            p_ErrMsg = ex.Message; 
            return null; 
        } 
       
    }

        /// <summary>
        /// Transfer data from data reader to data table
        /// </summary>
        /// <param name="reader">Data Reader</param>
        /// <returns>Datatable from the datareader</returns>
        /// <remarks></remarks>
        private static DataTable GetTable(IDataReader reader)
        {
            DataTable dtValue = new DataTable();
            DataColumn dc;
            List<string> arrList = new List<string>();
            int i;

                DataTable dtSchema = reader.GetSchemaTable();

                for (i = 0; i <= dtSchema.Rows.Count - 1; i++)
                {
                    dc = new DataColumn();
                    if (!dtValue.Columns.Contains(Convert.ToString(dtSchema.Rows[i]["ColumnName"])))
                    {
                        dc.ColumnName = Convert.ToString(dtSchema.Rows[i]["ColumnName"]);
                        arrList.Add(dc.ColumnName);
                        dtValue.Columns.Add(dc);
                    }
                }

                while (reader.Read())
                {
                    DataRow dr = dtValue.NewRow();
                    for (i = 0; i <= arrList.Count - 1; i++)
                    {
                        dr[Convert.ToString(arrList[i])] = reader[Convert.ToString(arrList[i])];
                    }
                    dtValue.Rows.Add(dr);
                }
            reader.Close();
            return dtValue;
            }
    }
}
